package cn.example.practice.db;

import cn.example.practice.model.Goods;
import cn.example.practice.util.JDBCUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * 商品数据访问对象，处理与商品相关的数据库操作
 */
public class GoodsDao {
    private static final Logger LOGGER = Logger.getLogger(GoodsDao.class.getName());

    private static final String SELECT_ALL_GOODS = "SELECT * FROM goods";
    private static final String SELECT_GOODS_BY_ID = "SELECT * FROM goods WHERE id = ?";
    private static final String SELECT_GOODS_BY_IDS = "SELECT * FROM goods WHERE id IN ";
    private static final String INSERT_GOODS = "INSERT INTO goods (name, price, image_data, image_type, stock, description) VALUES (?, ?, ?, ?, ?, ?)";
    private static final String UPDATE_GOODS = "UPDATE goods SET name = ?, price = ?, image_data = ?, image_type = ?, stock = ?, description = ? WHERE id = ?";
    private static final String DELETE_GOODS = "DELETE FROM goods WHERE id = ?";

    /**
     * 从ResultSet创建Goods对象
     */
    private Goods createGoodsFromResultSet(ResultSet rs) throws SQLException {
        Goods goods = new Goods();
        goods.setId(rs.getInt("id"));
        goods.setName(rs.getString("name"));
        goods.setPrice(rs.getDouble("price"));
        goods.setImageData(rs.getString("image_data"));
        goods.setImageType(rs.getString("image_type"));
        goods.setStock(rs.getInt("stock"));
        goods.setDescription(rs.getString("description"));
        return goods;
    }

    /**
     * 获取所有商品列表
     *
     * @return 商品列表
     */
    public List<Goods> getAllGoods() {
        List<Goods> goodsList = new ArrayList<>();
        try (ResultSet rs = JDBCUtils.executeQuery(SELECT_ALL_GOODS)) {
            while (rs.next()) {
                goodsList.add(createGoodsFromResultSet(rs));
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "Error retrieving all goods", e);
        }
        return goodsList;
    }

    /**
     * 根据ID获取商品
     *
     * @param id 商品ID
     * @return 包含商品的Optional对象
     */
    public Optional<Goods> getGoodsById(int id) {
        try (ResultSet rs = JDBCUtils.executeQuery(SELECT_GOODS_BY_ID, id)) {
            if (rs.next()) {
                return Optional.of(createGoodsFromResultSet(rs));
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "Error retrieving goods by id: " + id, e);
        }
        return Optional.empty();
    }

    /**
     * 根据ID列表获取多个商品
     *
     * @param ids 商品ID列表
     * @return 商品列表
     */
    public List<Goods> getGoodsByIds(List<Integer> ids) {
        List<Goods> goodsList = new ArrayList<>();
        if (ids.isEmpty()) {
            return goodsList;
        }

        StringBuilder sql = new StringBuilder(SELECT_GOODS_BY_IDS);
        sql.append("(").append(String.join(",", ids.stream().map(String::valueOf).toArray(String[]::new))).append(")");

        try (ResultSet rs = JDBCUtils.executeQuery(sql.toString())) {
            while (rs.next()) {
                goodsList.add(createGoodsFromResultSet(rs));
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "Error retrieving goods by ids", e);
        }
        return goodsList;
    }

    /**
     * 添加新商品
     *
     * @param goods 要添加的商品对象
     * @return 是否添加成功
     */
    public boolean addGoods(Goods goods) {
        try {
            return JDBCUtils.executeUpdate(INSERT_GOODS,
                    goods.getName(),
                    goods.getPrice(),
                    goods.getImageData(),
                    goods.getImageType(),
                    goods.getStock(),
                    goods.getDescription()) > 0;
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "Error adding goods", e);
            return false;
        }
    }

    /**
     * 更新商品信息
     *
     * @param goods 要更新的商品对象
     * @return 是否更新成功
     */
    public boolean updateGoods(Goods goods) {
        try {
            return JDBCUtils.executeUpdate(UPDATE_GOODS,
                    goods.getName(),
                    goods.getPrice(),
                    goods.getImageData(),
                    goods.getImageType(),
                    goods.getStock(),
                    goods.getDescription(),
                    goods.getId()) > 0;
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "Error updating goods", e);
            return false;
        }
    }

    /**
     * 删除商品
     *
     * @param id 要删除的商品ID
     * @return 是否删除成功
     */
    public boolean deleteGoods(int id) {
        try {
            return JDBCUtils.executeUpdate(DELETE_GOODS, id) > 0;
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "Error deleting goods", e);
            return false;
        }
    }
}
